﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Xml;
using IDAdmin.Lib.Utils;
using System.Web;
using System.Web.Script.Serialization;
using System.ComponentModel;

namespace IDAdmin.Lib.DataLayer
{
    public class WebDB
    {

        ///////////////////////////////////////////////////////////////////////////

        #region Authentication

        /// <summary>
        /// Kiểm tra thông tin đăng nhập của User (user phải có level từ 50 trở lên và đã kích hoạt email).
        /// Hàm trả về DataRow chứa thông tin ID, Level của User nếu hợp lệ, ngược lại trả về null
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        /// <param name="Ip"></param>
        /// <returns></returns>
        public static DataRow User_Validate(string UserName, string Password, string Ip)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Login", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Password", SqlDbType.NVarChar, Password));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@IP", SqlDbType.NVarChar, Ip));

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy mã game được chỉ định đối với User
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static string User_GetGameID(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_GetGameID", connection);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    return IDAdmin.Lib.Utils.Converter.ToString(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Quản lý tài khoản

        /// <summary>
        /// Tìm kiếm và lấy danh sách tài khoản của user.
        /// (FindValue="": Bỏ qua tham số. Status = -100: bỏ qua tham số. Level =-1 : Bỏ qua tham số)
        /// </summary>
        /// <param name="FindValue">CustomerID hoặc UserName cần tìm (rỗng nếu bỏ qua)</param>
        /// <param name="Status">Trạng thái (-1: khóa, 0: chưa kích hoạt, 1: đã kích hoạt, giá trị khác = toàn bộ)</param>
        /// <param name="Level">level quy định loại user (truyền giá trị âm nếu bỏ qua tham số này khi tìm kiếm)</param>
        /// <param name="StartPos">Vị trí</param>
        /// <param name="PageSize">Số lượng hiển thị trên 1 trang</param>
        /// <returns></returns>
        public static DataTable User_Select(string FindValue, int Status, int Level, int StartPos, int PageSize)
        {
            try
            {
                //if (FindValue != "")
                //{
                //    FindValue = "%" + FindValue + "%";
                //}

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FindValue", SqlDbType.NVarChar, FindValue));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Status", SqlDbType.Int, Status));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Level", SqlDbType.Int, Level));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartPos", SqlDbType.Int, StartPos));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PageSize", SqlDbType.Int, PageSize));
                    return DbHelper.FillDataTable(cmd, Meta.USER);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable Gosu_User_Select(string FindValue, int StartPos, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GosuAccountList", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Username", SqlDbType.NVarChar, FindValue));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartPos", SqlDbType.Int, StartPos));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PageSize", SqlDbType.Int, PageSize));
                    return DbHelper.FillDataTable(cmd, Meta.USER);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable Gosu_Summary(DateTime from, DateTime to)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_Gosu", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateFrom", SqlDbType.DateTime, from));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateTo", SqlDbType.DateTime, to));
                    return DbHelper.FillDataTable(cmd, Meta.USER);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static long Gosu_Total()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_TotalGosu", connection);
                    return Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy DataRow chứa thông tin chi tiết của 1 user
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="CustomerID"></param>
        /// <returns></returns>
        public static DataRow User_Details(string CustomerID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_Details", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CustomerID", SqlDbType.NVarChar, CustomerID));
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Điểm tích lũy của user
        /// </summary>
        /// <returns></returns>
        public static int User_AccumulationPoint(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_AccPoint_Get", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    return Utils.Converter.ToInt(cmd.ExecuteScalar());
                }
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// Thay đổi mật khẩu của user
        /// </summary>
        /// <param name="LoginName">Tên user đang đăng nhập</param>
        /// <param name="CustomerID">ID của user cần đổi pass</param>
        /// <param name="Password">Password</param>
        /// <returns></returns>
        public static void User_ChangePassword(string LoginName, string CustomerID, string Password)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_ChangePassword", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CustomerID", SqlDbType.NVarChar, CustomerID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Password", SqlDbType.NVarChar, Password));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật thông tin của User
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="ID"></param>
        /// <param name="CustomerID"></param>
        /// <param name="Email"></param>
        /// <param name="NumberID"></param>
        /// <param name="Name"></param>
        /// <param name="Level"></param>
        /// <param name="PhoneNumber"></param>
        /// <param name="Address"></param>
        /// <param name="Birthday"></param>
        /// <param name="Gender"></param>
        /// <param name="LocationID"></param>
        /// <param name="Status"></param>
        /// <param name="QuestionID"></param>
        /// <param name="Answer"></param>
        public static void User_ChangeInfo(string LoginName, long ID, string CustomerID, string Email,
                                    string NumberID, string Name, int Level, string PhoneNumber, string Address,
                                    DateTime Birthday, bool Gender, int LocationID, int Status,
                                    int QuestionID, string Answer)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_ChangeInfo", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CustomerID", SqlDbType.NVarChar, CustomerID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Email", SqlDbType.NVarChar, Email));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@NumberID", SqlDbType.NVarChar, NumberID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Name", SqlDbType.NVarChar, Name));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Level", SqlDbType.Int, Level));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PhoneNumber", SqlDbType.NVarChar, PhoneNumber));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Address", SqlDbType.NVarChar, Address));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Birthday", SqlDbType.DateTime, Birthday));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Gender", SqlDbType.Bit, Gender));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LocationID", SqlDbType.Int, LocationID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Status", SqlDbType.Int, Status));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@QuestionID", SqlDbType.Int, QuestionID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Answer", SqlDbType.NVarChar, Answer));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Kiểm tra tài khoản tồn tại hay không
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool User_Exists(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_CheckExists", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    return Utils.Converter.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable ChanellingUser_Select(string Community, string UserName, int Page, int PageSize)
        {
            {
                try
                {
                    using (SqlConnection connection = ConnectionHelper.GetConnection())
                    {
                        SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingUser_Select", connection);
                        cmd.Parameters.AddWithValue("@Community", Community);
                        cmd.Parameters.AddWithValue("@UserName", UserName);
                        cmd.Parameters.AddWithValue("@Page", Page);
                        cmd.Parameters.AddWithValue("@PageSize", PageSize);

                        return DbHelper.FillDataTable(cmd, "ChanellingUser");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Bài viết

        /// <summary>
        /// Lấy danh sách 10 bài viết tính từ bài viết có ID là StartID 
        /// (StartID = 0: Lấy từ bài viết sau cùng nhất)
        /// </summary>
        /// <param name="StartID"></param>
        /// <returns></returns>
        public static DataTable News_Select(long StartID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartID", SqlDbType.BigInt, StartID));
                    return DbHelper.FillDataTable(cmd, Meta.NEWS);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy thông tin chi tiết 1 bài viết
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataRow News_Details(long ID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Details", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Bổ sung bài viết
        /// </summary>
        /// <param name="Title"></param>
        /// <param name="Content"></param>
        /// <param name="Category"></param>
        /// <param name="Writter"></param>
        public static void News_Insert(string Title, string Content, int Category, string Writter, string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Insert", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Title", SqlDbType.NVarChar, Title));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Content", SqlDbType.NText, Content));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Category", SqlDbType.Int, Category));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Writter", SqlDbType.NVarChar, Writter));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, GameID));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật bài viết
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Title"></param>
        /// <param name="Content"></param>
        /// <param name="Category"></param>
        /// <param name="Writter"></param>
        public static void News_Update(long ID, string Title, string Content, int Category, string Writter, string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Update", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Title", SqlDbType.NVarChar, Title));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Content", SqlDbType.NText, Content));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Category", SqlDbType.Int, Category));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Writter", SqlDbType.NVarChar, Writter));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, GameID));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Xóa bài viết
        /// </summary>
        /// <param name="ID"></param>
        public static void News_Delete(long ID, string LoginName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Delete", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Giao dịch

        /// <summary>
        /// Tra cứu giao dịch nạp vàng vào game
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Type"></param>
        /// <param name="Server"></param>
        /// <param name="PinNumber"></param>
        /// <param name="Account"></param>
        /// <param name="StartPos"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable CardLog_Select(DateTime? FromDate, DateTime? ToDate, string Type, string Server, string PinNumber, string Account, string PayPurpose, long StartPos, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_CardLog_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FromDate", SqlDbType.DateTime, FromDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ToDate", SqlDbType.DateTime, ToDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Type", SqlDbType.NVarChar, Type));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, AppManager.GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ServerName", SqlDbType.NVarChar, Server));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PinNumber", SqlDbType.NVarChar, PinNumber));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Account", SqlDbType.NVarChar, Account));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PayPurpose", SqlDbType.NVarChar, PayPurpose));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartPos", SqlDbType.BigInt, StartPos));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PageSize", SqlDbType.Int, PageSize));
                    return DbHelper.FillDataTable(cmd, Meta.CARDLOG);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Chi tiết giao dịch
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataRow CardLog_Details(long ID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_CardLog_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", ID);

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lịch sử quá trình xử lý giao dịch
        /// </summary>
        /// <param name="ExchangeID"></param>
        /// <returns></returns>
        public static DataTable PaymentGateLog_Select(string ExchangeID)
        {
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand("spAdmin_PaymentGateLog_Select", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ExchangeID", ExchangeID);

                return DbHelper.FillDataTable(cmd, "PaymentGateLog");
            }
        }

        /// <summary>
        /// Tra log giao dịch theo lỗi
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Type"></param>
        /// <param name="ErrorCode"></param>
        /// <returns></returns>
        public static DataTable CardLog_SelectError(DateTime? FromDate, DateTime? ToDate, string Type, int Status, int ErrorCode)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_CardLog_GetErrorLog", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, AppManager.GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FromDate", SqlDbType.DateTime, FromDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ToDate", SqlDbType.DateTime, ToDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Type", SqlDbType.NVarChar, Type));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Status", SqlDbType.Int, Status));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ErrorCode", SqlDbType.Int, ErrorCode));

                    return DbHelper.FillDataTable(cmd, Meta.CARDLOG);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// cập nhật lại cardlog
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="ServerName"></param>
        /// <param name="Amount"></param>
        /// <param name="Status"></param>
        /// <param name="ErrorCode"></param>
        public static void CardLog_Update(long ID, string ServerName, int Amount, int Status, int ErrorCode)
        {
            try
            {
                using (SqlConnection connnection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_CardLog_Update", connnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", ID);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@Amount", Amount);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@ErrorCode", ErrorCode);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tra cứu lịch sử nạp tiền vào ví GOSU
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Type"></param>
        /// <param name="PinNumber"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable WalletRechargeLog_Select(DateTime? FromDate, DateTime? ToDate, string Type, string PinNumber, string Account, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletRechargeLog_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@Type", Type);
                    cmd.Parameters.AddWithValue("@PinNumber", PinNumber);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "WalletRechargeLog");

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tra cứu lịch sử mua CODE bằng GOSU
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Kind"></param>
        /// <param name="Amount"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable WalletSpendingLog_Select(DateTime? FromDate, DateTime? ToDate, string Kind, int Amount, string Account, int Status, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletSpendingLog_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@CodeType", Kind);
                    cmd.Parameters.AddWithValue("@Amount", Amount);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    return DbHelper.FillDataTable(cmd, "WalletSpendingLog");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật log giao dịch. Hàm trả về:
        /// -99: Lỗi hệ thống
        ///  -1: Giao dịch không tồn tại hoặc không cho phép cập nhật  
        ///   0: Cập nhật không thành công
        ///   1: Cập nhật thành công
        /// </summary>
        /// <param name="RechargeID"></param>
        /// <param name="Amount"></param>
        /// <param name="Status"></param>
        /// <param name="ErrorCode"></param>
        /// <param name="Msg"></param>
        /// <param name="LoginName"></param>
        /// <param name="IP"></param>
        /// <returns></returns>
        public static int WalletRechargeLog_Update(long RechargeID, int Amount, int Status, int ErrorCode, string Msg, string LoginName, string LogAction, string IP)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletRechargeLog_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RechargeID", RechargeID);
                    cmd.Parameters.AddWithValue("@Amount", Amount);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@ErrorCode", ErrorCode);
                    cmd.Parameters.AddWithValue("@Msg", Msg);
                    cmd.Parameters.AddWithValue("@LoginName", LoginName);
                    cmd.Parameters.AddWithValue("@LogAction", LogAction);
                    cmd.Parameters.AddWithValue("@IP", IP);

                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                    pResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pResult);

                    DbHelper.ExecuteNonQuery(cmd);
                    return Lib.Utils.Converter.ToInt(pResult.Value, -99);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Chi tiết giao dịch nạp tiền vào ví GOSU
        /// </summary>
        /// <param name="RechargeID"></param>
        /// <returns></returns>
        public static DataRow WalletRechargeLog_Details(long RechargeID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletRechargeLog_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RechargeID", RechargeID);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tra cứu log giao dịch thanh toán qua dịch vụ thanh toán OAuth
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="ServiceID"></param>
        /// <param name="SearchValue"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable OAuthPaymentLog_Select(DateTime? FromDate, DateTime? ToDate, string ServiceID, string SearchValue, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentLog_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@SearchValue", SearchValue);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    return DbHelper.FillDataTable(cmd, "OAuthPaymentLog");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Chi tiết giao dịch
        /// </summary>
        /// <param name="LogID"></param>
        /// <returns></returns>
        public static DataRow OAuthPaymentLog_Details(long LogID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentLog_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@LogID", LogID);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Hoạt động - Sự kiện

        /// <summary>
        /// Lấy danh sách các user, điểm hảo hữu và số lượng giftcode đã cấp phát cho user
        /// </summary>
        /// <param name="GameID"></param>
        /// <param name="FindValue"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable UserFriend_Select(string GameID, string FindValue, int Page, int PageSize)
        {
            try
            {
                FindValue = FindValue.Trim();
                if (FindValue != "")
                {
                    FindValue = "%" + FindValue + "%";
                }
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_UserFriend_Select", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.AddWithValue("@FindValue", FindValue);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "UserFriend");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cấp GiftCode cho User
        /// </summary>
        /// <param name="GameID"></param>
        /// <param name="ServerName"></param>
        /// <param name="UserName"></param>
        /// <param name="ReceivedType"></param>
        /// <returns></returns>
        public static bool GiveGiftCode(string GameID, string ServerName, string UserName, int ReceivedType)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_GiftCode_Set", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@ReceivedType", ReceivedType);

                    SqlParameter paraResult = new SqlParameter("@Result", SqlDbType.Int);
                    paraResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(paraResult);

                    cmd.ExecuteNonQuery();

                    return IDAdmin.Lib.Utils.Converter.ToInt(paraResult.Value) == 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Đếm số GiftCode chưa cấp
        /// </summary>
        /// <param name="GameID"></param>
        /// <returns></returns>
        public static long GiftCode_CountFree()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GiftCode_CountFree", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);

                    return Lib.Utils.Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch
            {
                return -1;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Thống kê

        /// <summary>
        /// Thống kê số lượng User
        /// </summary>
        /// <returns></returns>
        public static long Statistics_UserAccount(string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_CountOfUser", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    return Utils.Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static long Statistics_ActivatedUser(string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_CountOfActivatedUser", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    return Utils.Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê kích hoạt dựa trên Kind: Free, Trial, VIP, NORMAL...
        /// </summary>
        /// <param name="GameID"></param>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="kind"></param>
        /// <returns></returns>
        public static DataTable Statistics_ActivatedUser(string GameID, DateTime StartDate, DateTime EndDate, string kind)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_ActivatedUserByDate", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateFrom", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateTo", SqlDbType.DateTime, EndDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Kind", SqlDbType.VarChar, kind));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê CDKey sử dụng theo ngày (Sử dụng cho report tới SG và CUBI)
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="kind"></param>
        /// <returns></returns>
        public static DataTable Statistics_CDKey(DateTime StartDate, DateTime EndDate, string kind)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_CACK_CDKey_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FromDate", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ToDate", SqlDbType.DateTime, EndDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Kind", SqlDbType.VarChar, kind));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê số lượng User đăng ký trong ngày
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public static DataTable Statistics_UserAccount(string GameID, DateTime StartDate, DateTime EndDate)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_RegUserByDate", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateFrom", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateTo", SqlDbType.DateTime, EndDate));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê doanh thu hàng ngày
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="TimeLine"></param>
        /// <param name="Server"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public static DataTable Statistics_SumaryByDate(DateTime StartDate, DateTime EndDate, int TimeLine, string Server, string Type)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistic_SumaryByDate", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartDate", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@EndDate", SqlDbType.DateTime, EndDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@TimeLine", SqlDbType.Int, TimeLine));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, AppManager.GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ServerName", SqlDbType.NVarChar, Server));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Type", SqlDbType.NVarChar, Type));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê top nộp tiền
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="Server"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable Statistic_TopRecharge(DateTime? StartDate, DateTime? EndDate, string Server, string Account, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistics_SumCardLogByUser", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", StartDate);
                    cmd.Parameters.AddWithValue("@ToDate", EndDate);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Server", Server);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê giao dịch của khách hàng phía đối tác Chanelling
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="Server"></param>
        /// <param name="Community"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable Statistic_TopChanellingRecharge(DateTime? StartDate, DateTime? EndDate, string Server, string Community, string Account, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistics_SumCardLogByChanellingUser", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", StartDate);
                    cmd.Parameters.AddWithValue("@ToDate", EndDate);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Server", Server);
                    cmd.Parameters.AddWithValue("@Community", Community);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê tổng hợp tiền nạp vào game theo từng game và từng hình thức thanh toán
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_SumaryByGameAndType(DateTime? FromDate, DateTime? ToDate)
        {
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand("spAdmin_Statistic_SumaryByGameAndType", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FromDate", FromDate);
                cmd.Parameters.AddWithValue("@ToDate", ToDate);
                return DbHelper.FillDataTable(cmd, "Statistic");
            }
        }

        /// <summary>
        /// Thống kê tổng hợp giao dịch nạp tiền vào ví GOSU
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_SumaryWalletRecharge(DateTime FromDate, DateTime ToDate)
        {
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand("spAdmin_Statistic_SumaryWalletRecharge", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FromDate", FromDate);
                cmd.Parameters.AddWithValue("@ToDate", ToDate);
                return DbHelper.FillDataTable(cmd, "Statistic");
            }
        }

        /// <summary>
        /// Thống kê nạp tiền theo cộng đồng
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_SumaryByCommunity(DateTime FromDate, DateTime ToDate, string server)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistic_SumaryByCommunity", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@ServerName", server);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_OAuthPayment(string ServiceID, DateTime FromDate, DateTime ToDate)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistic_OAuthPayment", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        #region Bill nạp tiền trực tiếp

        /// <summary>
        /// Danh sách hóa đơn nạp tiền chưa được duyệt
        /// </summary>
        /// <returns></returns>
        public static DataTable Bill_SelectNotAccept()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_SelectNotAccept", connection);
                    return DbHelper.FillDataTable(cmd, Meta.BILL);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy thông tin của hóa đơn (chưa được duyệt) để xử lý
        /// </summary>
        /// <param name="BillID"></param>
        /// <returns></returns>
        public static DataRow Bill_DetailsForAccept(long BillID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_DetailsForAccept", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@BillID", SqlDbType.BigInt, BillID));
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tạo hóa đơn nạp tiền
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="Account"></param>
        /// <param name="GameServer"></param>
        /// <param name="Amount">Số tiền thu của khách hàng</param>
        /// <param name="CardLogAmount">Số tiền thực nạp vào game (sau khi đã cộng thêm %)</param>
        public static void Bill_Create(string LoginName, string Account, string GameID, string GameServer, int Amount, int CardLogAmount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_Create", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Account", SqlDbType.NVarChar, Account));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameServer", SqlDbType.NVarChar, GameServer));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Amount", SqlDbType.Int, Amount));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CardLogAmount", SqlDbType.Int, CardLogAmount));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Xóa hóa đơn nạp tiền
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="BillID"></param>
        public static void Bill_Delete(string LoginName, long BillID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_Delete", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@BillID", SqlDbType.BigInt, BillID));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Duyệt chấp nhận hóa đơn nạp tiền
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="BillID"></param>
        /// <param name="IncRate"></param>
        /// <param name="IPAddress"></param>
        public static void Bill_Accept(string LoginName, long BillID, string IPAddress)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_Accept", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@BillID", SqlDbType.BigInt, BillID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@IPAddress", SqlDbType.NVarChar, IPAddress));

                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Nạp tiền trực tiếp vào ví tiền
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="RechargeUser"></param>
        /// <param name="Amount"></param>
        /// <param name="IP"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static bool Wallet_AddDirectly(string UserName, string RechargeUser, int Amount, string IP, string Msg, string Type)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmdInit = new SqlCommand("sp_Wallet_RechargeInit", connection);
                    cmdInit.CommandType = CommandType.StoredProcedure;
                    cmdInit.Parameters.AddWithValue("@UserName", UserName);
                    cmdInit.Parameters.AddWithValue("@RechargeUser", RechargeUser);
                    cmdInit.Parameters.AddWithValue("@Type", Type);
                    cmdInit.Parameters.AddWithValue("@Serial", "");
                    cmdInit.Parameters.AddWithValue("@PinNumber", "");
                    cmdInit.Parameters.AddWithValue("@IP", IP);

                    long rechargeID = Utils.Converter.ToLong(cmdInit.ExecuteScalar(), -1);

                    if (rechargeID <= 0)
                    {
                        return false;
                    }
                    else
                    {
                        SqlCommand cmdFinish = new SqlCommand("sp_Wallet_RechargeFinish", connection);
                        cmdFinish.CommandType = CommandType.StoredProcedure;
                        cmdFinish.Parameters.AddWithValue("@RechargeID", rechargeID);
                        cmdFinish.Parameters.AddWithValue("@Amount", Amount);
                        cmdFinish.Parameters.AddWithValue("@Status", 1);
                        cmdFinish.Parameters.AddWithValue("@ErrorCode", 0);
                        cmdFinish.Parameters.AddWithValue("@Msg", Msg);
                        cmdFinish.Parameters.AddWithValue("@Voucher", "");
                        SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                        pResult.Direction = ParameterDirection.Output;
                        cmdFinish.Parameters.Add(pResult);

                        cmdFinish.ExecuteNonQuery();

                        int result = Utils.Converter.ToInt(pResult.Value);
                        if (result <= 0)
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        #region Khác

        /// <summary>
        /// Danh sách Game
        /// </summary>
        /// <returns></returns>
        public static DataTable Game_Select()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_Game_Select", connection);
                    return DbHelper.FillDataTable(cmd, "Game");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Danh sách game được cấp quyền quản trị cho user
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static DataTable Game_SelectForAdmin(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Game_SelectForAdmin", connection);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    return DbHelper.FillDataTable(cmd, "Game");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable Server_Select(bool addAllRow)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_GameServer_Select", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    if (addAllRow)
                    {
                        DataRow dr = dt.NewRow();
                        dr[Lib.Meta.SERVER_NAME] = "";
                        dr[Lib.Meta.SERVER_FULLNAME] = "---- Tất cả --";
                        dt.Rows.InsertAt(dr, 0);
                    }
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetActiveCode(int page, int pagesize, string pincode, string serial, string type, string kind, int turn, int status, string account, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetActiveCode", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Page", page);
                    cmd.Parameters.AddWithValue("@PageSize", pagesize);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Serial", serial);
                    cmd.Parameters.AddWithValue("@Type", type);
                    cmd.Parameters.AddWithValue("@Kind", kind);
                    cmd.Parameters.AddWithValue("@Turn", turn);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@Account", account);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetVoucherCode(int page, int pagesize, string pincode, string rate, int status, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetVoucherCode", connection);
                    cmd.Parameters.AddWithValue("@Page", page);
                    cmd.Parameters.AddWithValue("@PageSize", pagesize);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Rate", rate);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetActiveCodeBySerial(string serial, string account)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetActiveCodeBySerial", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Serial", serial);
                    cmd.Parameters.AddWithValue("@Account", account);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int GetTotalActiveCode(string pincode, string serial, string type, string kind, int turn, int status, string account, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetTotalActiveCode", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Serial", serial);
                    cmd.Parameters.AddWithValue("@Type", type);
                    cmd.Parameters.AddWithValue("@Kind", kind);
                    cmd.Parameters.AddWithValue("@Turn", turn);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@Account", account);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                    pResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pResult);
                    DbHelper.ExecuteNonQuery(cmd);
                    return Lib.Utils.Converter.ToInt(pResult.Value, -99);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int GetTotalVoucherCode(string pincode, string rate, int status, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetTotalVoucherCode", connection);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Rate", rate);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                    pResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pResult);
                    DbHelper.ExecuteNonQuery(cmd);
                    return Lib.Utils.Converter.ToInt(pResult.Value, -99);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataRow Server_Details(int ServerID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_GameServer_DetailsByID", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch
            {
                return null;
            }
        }

        public static void Server_Insert(int ServerID, string ServerName, string FullName, string ServerIdentityName, int SortOrder)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GameServer_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@FullName", FullName);
                    cmd.Parameters.AddWithValue("@ServerIdentityName", ServerIdentityName);
                    cmd.Parameters.AddWithValue("@SortOrder", SortOrder);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void Server_Update(int ServerID, string ServerName, string FullName, string ServerIdentityName,
                                         int SortOrder, int ServerStatus, int IsHot,
                                         string DNS, string GoldTransferLink, string IDCheckLink, string GetCharacterLink, string LoginLink)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GameServer_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@FullName", FullName);
                    cmd.Parameters.AddWithValue("@ServerIdentityName", ServerIdentityName);
                    cmd.Parameters.AddWithValue("@SortOrder", SortOrder);
                    cmd.Parameters.AddWithValue("@ServerStatus", ServerStatus);
                    cmd.Parameters.AddWithValue("@IsHot", IsHot);
                    cmd.Parameters.AddWithValue("@DNS", DNS);
                    cmd.Parameters.AddWithValue("@GoldTransferLink", GoldTransferLink);
                    cmd.Parameters.AddWithValue("@IDCheckLink", IDCheckLink);
                    cmd.Parameters.AddWithValue("@GetCharacterLink", GetCharacterLink);
                    cmd.Parameters.AddWithValue("@LoginLink", LoginLink);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public static void Server_UpdateStatus(int ServerID, string GameID, int Status)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GameServer_UpdateStatus", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.AddWithValue("@ServerStatus", Status);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Danh sách tỉnh/thành phố
        /// </summary>
        /// <returns></returns>
        public static DataTable Location_Select()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_Location_Select", connection);
                    return DbHelper.FillDataTable(cmd, Meta.LOCATION);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Danh sách câu hỏi bí mật
        /// </summary>
        /// <returns></returns>
        public static DataTable Question_Select()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_Question_Select", connection);
                    return DbHelper.FillDataTable(cmd, Meta.LOCATION);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Kiểm ra quyền của user trên một chức năng xem user có được cấp quyền hay không
        /// </summary>
        /// <param name="FunctionName"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool Function_CheckRight(string FunctionName, string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Function_CheckRight", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FunctionName", SqlDbType.NVarChar, FunctionName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    return Utils.Converter.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Lấy danh sách các chức năng và quyền được cấp cho user trên mỗi chức năng
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static DataTable Function_SelectUserRight(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Function_SelectUserRight", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    return DbHelper.FillDataTable(cmd, "AdminFunction_User");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cấp/hủy quyền cho user trên chức năng của hệ thống
        /// </summary>
        /// <param name="FunctionName"></param>
        /// <param name="UserName"></param>
        /// <param name="Allow"></param>
        public static void Function_SetUserRight(string FunctionName, string UserName, bool Allow)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Function_SetUserRight", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FunctionName", SqlDbType.NVarChar, FunctionName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Allow", SqlDbType.Bit, Allow));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy danh sách các User đã được cấp quyền quản trị hệ thống
        /// </summary>
        /// <returns></returns>
        public static DataTable Function_SelectGrantedUser()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Function_SelectGrantedUser", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    return DbHelper.FillDataTable(cmd, "GrantedUser");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Xem log giao dịch của User
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable SelectAdminLog(string UserName, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_SelectAdminLog", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "AdminLog");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Ghi log thao tác của User
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="IPAddress"></param>
        /// <param name="LogAction"></param>
        public static void WriteLog(string UserName, string IPAddress, string LogAction)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_WriteLog", connection);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@IPAddress", IPAddress);
                    cmd.Parameters.AddWithValue("@LogAction", LogAction);
                    cmd.ExecuteNonQuery();
                }
            }
            catch // (Exception ex)
            {
                //throw ex;
            }
        }

        public static string Write_CACK_EventLog(string UserName, string ServerName, string AdditionalData)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_CACK_EventLog_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@EventName", "EVENT000");
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@servername", ServerName);
                    cmd.Parameters.AddWithValue("@AdditionalData", AdditionalData);
                    cmd.Parameters.AddWithValue("@IsFinish", 1);
                    cmd.ExecuteNonQuery();
                    return "1";
                }
            }
            catch (Exception ex)
            {
                return "0";
            }
        }

        public static void LockUnlockAccount(string Username, int Status, string Note)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_LockUnlockAccount", connection);
                    cmd.Parameters.AddWithValue("@Username", Username);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@Note", Note);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {

            }
        }

        public static DataTable GetLockUnlockAccount(string Username)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GetLockUnlockAccount", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", Username);
                    return DbHelper.FillDataTable(cmd, "LockUnlockAccount");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Chanelling

        /// <summary>
        /// Lấy danh sách đối tác chanelling theo game
        /// </summary>
        /// <param name="gameID"></param>
        /// <returns></returns>
        public static DataTable ChanellingPartner_SelectByGame(string gameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingPartner_SelectByGame", connection);
                    cmd.Parameters.AddWithValue("@GameID", gameID);
                    return DbHelper.FillDataTable(cmd, "ChanellingPartner");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy danh sách server game được cấp cho đối tác chanelling
        /// </summary>
        /// <param name="partnerID"></param>
        /// <returns></returns>
        public static DataTable ChanellingGameServer_SelectByPartner(string partnerID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_SelectByPartner", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    return DbHelper.FillDataTable(cmd, "ChanellingGameServer");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thông tin chi tiết server
        /// </summary>
        /// <param name="partnerID"></param>
        /// <param name="serverName"></param>
        /// <returns></returns>
        public static DataRow ChanellingGameServer_Details(string partnerID, string serverName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_Details", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="partnerID"></param>
        /// <param name="serverName"></param>
        /// <param name="playEnabled"></param>
        /// <param name="topupEnabled"></param>
        /// <param name="notes"></param>
        public static void ChanellingGameServer_ChangeStatus(string partnerID, string serverName, int playEnabled, int topupEnabled, string notes)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_ChangeStatus", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    cmd.Parameters.AddWithValue("@PlayEnabled", playEnabled);
                    cmd.Parameters.AddWithValue("@TopupEnabled", topupEnabled);
                    cmd.Parameters.AddWithValue("@Notes", notes);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="partnerID"></param>
        /// <param name="serverName"></param>
        /// <param name="gameID"></param>
        public static void ChanellingGameServer_Delete(string partnerID, string serverName, string gameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_Delete", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    cmd.Parameters.AddWithValue("@GameID", gameID);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int ChanellingGameServer_Add(string partnerID, string serverName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_Add", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    int rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        #endregion

        #region OAuth - Payment Service

        /// <summary>
        /// Danh sách các OAuthApplication
        /// </summary>
        /// <param name="SearchValue"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable OAuthApplication_Select(string SearchValue, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@SearchValue", SearchValue);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "OAuthApplication");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thông tin chi tiết Application
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataRow OAuthApplication_Details(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Application đã tồn tại?
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthApplication_Exists(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Exists", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);

                    return Convert.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Bổ sung ứng dụng
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="ClientID"></param>
        /// <param name="Secret"></param>
        /// <param name="ApplicationName"></param>
        /// <param name="Site"></param>
        /// <param name="Logo"></param>
        /// <param name="Enabled"></param>
        /// <param name="AlwaysTrust"></param>
        /// <returns></returns>
        public static bool OAuthApplication_Insert(string UserName, string ClientID, string Secret, string ApplicationName, string Site, string Logo, bool Enabled, bool AlwaysTrust, string CssLink, string PopupCssLink, string JsLink, string RegSourceID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    cmd.Parameters.AddWithValue("@Secret", Secret);
                    cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
                    cmd.Parameters.AddWithValue("@Site", Site);
                    cmd.Parameters.AddWithValue("@Logo", Logo);
                    cmd.Parameters.AddWithValue("@Enabled", Enabled);
                    cmd.Parameters.AddWithValue("@AlwaysTrust", AlwaysTrust);
                    cmd.Parameters.AddWithValue("@CssLink", CssLink);
                    cmd.Parameters.AddWithValue("@PopupCssLink", PopupCssLink);
                    cmd.Parameters.AddWithValue("@JsLink", JsLink);
                    cmd.Parameters.AddWithValue("@RegSourceID", RegSourceID);

                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật thông tin ứng dụng
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="ClientID"></param>
        /// <param name="Secret"></param>
        /// <param name="ApplicationName"></param>
        /// <param name="Site"></param>
        /// <param name="Logo"></param>
        /// <param name="Enabled"></param>
        /// <param name="AlwaysTrust"></param>
        /// <returns></returns>
        public static bool OAuthApplication_Update(string UserName, string ClientID, string Secret, string ApplicationName, string Site, string Logo, bool Enabled, bool AlwaysTrust, string CssLink, string PopupCssLink, string JsLink, string RegSourceID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    cmd.Parameters.AddWithValue("@Secret", Secret);
                    cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
                    cmd.Parameters.AddWithValue("@Site", Site);
                    cmd.Parameters.AddWithValue("@Logo", Logo);
                    cmd.Parameters.AddWithValue("@Enabled", Enabled);
                    cmd.Parameters.AddWithValue("@AlwaysTrust", AlwaysTrust);
                    cmd.Parameters.AddWithValue("@CssLink", CssLink);
                    cmd.Parameters.AddWithValue("@PopupCssLink", PopupCssLink);
                    cmd.Parameters.AddWithValue("@JsLink", JsLink);
                    cmd.Parameters.AddWithValue("@RegSourceID", RegSourceID);

                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Danh sách RedirectURI của Application
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataTable OAuthApplication_SelectRedirectURI(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_SelectRedirectURI", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    return DbHelper.FillDataTable(cmd, "OAuthApplication_RedirectURI");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật RedirectURI
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="RedirectURI"></param>
        public static void OAuthApplication_UpdateRedirectURIs(string ClientID, string[] RedirectURIs)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_DeleteRedirectURIs", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    DbHelper.ExecuteNonQuery(cmd);

                    using (SqlCommand cmd1 = new SqlCommand("spAdmin_OAuthApplication_AddRedirectURI", connection))
                    {
                        cmd1.CommandType = CommandType.StoredProcedure;
                        SqlParameter pClientID = new SqlParameter("@ClientID", ClientID);
                        SqlParameter pRedirectURI = new SqlParameter("@RedirectURI", SqlDbType.NVarChar, 500);
                        cmd1.Parameters.Add(pClientID);
                        cmd1.Parameters.Add(pRedirectURI);
                        foreach (string uri in RedirectURIs)
                        {
                            if (!string.IsNullOrEmpty(uri.Trim()))
                            {
                                pRedirectURI.Value = uri.Trim();
                                cmd1.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataTable OAuthPaymentService_Select(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    return DbHelper.FillDataTable(cmd, "OAuthPayment_Service");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthPaymentService_Exists(string ServiceID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Exists", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);

                    return Convert.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataRow OAuthPaymentService_Details(string ServiceID, string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="ServiceName"></param>
        /// <param name="ServiceKey"></param>
        /// <param name="ServiceDesc"></param>
        /// <param name="GosuTransferType"></param>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthPaymentService_Insert(string ServiceID, string ServiceName, string ServiceKey, string ServiceDesc, string GosuTransferType, string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@ServiceName", ServiceName);
                    cmd.Parameters.AddWithValue("@ServiceKey", ServiceKey);
                    cmd.Parameters.AddWithValue("@ServiceDesc", ServiceDesc);
                    cmd.Parameters.AddWithValue("@GosuTransferType", GosuTransferType);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="ServiceName"></param>
        /// <param name="ServiceKey"></param>
        /// <param name="ServiceDesc"></param>
        /// <param name="GosuTransferType"></param>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthPaymentService_Update(string ServiceID, string ServiceName, string ServiceKey, string ServiceDesc, string GosuTransferType, string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@ServiceName", ServiceName);
                    cmd.Parameters.AddWithValue("@ServiceKey", ServiceKey);
                    cmd.Parameters.AddWithValue("@ServiceDesc", ServiceDesc);
                    cmd.Parameters.AddWithValue("@GosuTransferType", GosuTransferType);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        #endregion

        #region Admin Commands

        /// <summary>
        /// Thay đổi trạng thái của hình thức thanh toán
        /// </summary>
        /// <param name="Type"></param>
        /// <param name="Status"></param>
        public static void PaymentType_Update(string Type, int Status)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_PaymentType_Update", connection);
                    cmd.Parameters.AddWithValue("@Type", Type);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        /// <summary>
        /// Lấy dữ liệu ccu
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<Ccu> getCcu(List<DateTime> dateTimeList)
        {
            List<Ccu> result = new List<Ccu>();
            //DataTable table = new DataTable();
            //table.TableName = "Ccu";
            //PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(Ccu));
            //for (int i = 0; i < props.Count; i++)
            //{
               // PropertyDescriptor prop = props[i];
                //table.Columns.Add(prop.Name, prop.PropertyType);
            //}
            foreach (DateTime dt in dateTimeList)
            {
                string data = HttpHelper.HttpGetOther(string.Format("http://dev.gosu.vn/api/188/ccu.{0}.json", dt.ToString("yyyyMMdd")), 0);
                //string data = HttpHelper.HttpGetOther("http://dev.gosu.vn/api/188/ccu.20150208.json", 0);
                if (string.IsNullOrEmpty(data) == false)
                {
                    IList<Ccu> ccuList = new JavaScriptSerializer().Deserialize<IList<Ccu>>(data.Substring(7).Remove(data.Substring(7).Length - 1));
                    //string json = "{\"ccu\":[{\"GameZone\":\"1003\",\"Num\":\"2\",\"TimeStamp\":\"1422766800\"},{\"GameZone\":\"1003\",\"Num\":\"2\",\"TimeStamp\":\"1423285200\"},{\"GameZone\":\"1003\",\"Num\":\"1\",\"TimeStamp\":\"1423371600\"},{\"GameZone\":\"1004\",\"Num\":\"1\",\"TimeStamp\":\"1423285200\"},{\"GameZone\":\"1003\",\"Num\":\"3\",\"TimeStamp\":\"1423285500\"}] }";
                    //IList<Ccu> ccuList = new JavaScriptSerializer().Deserialize<IList<Ccu>>(json.Substring(7).Remove(json.Substring(7).Length - 1));
                    //object[] values = new object[props.Count];
                    //foreach (Ccu item in ccuList)
                    //{
                    // for (int i = 0; i < values.Length; i++)
                    //{
                    // values[i] = props[i].GetValue(item);
                    //}
                    //table.Rows.Add(values);
                    //}
                    result.AddRange(ccuList);
                }
            }
            //return table;
        return result;
        }

        /// <summary>
        /// Lấy dữ liệu ccu
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<Au> getAu(List<DateTime> dateTimeList)
        {
            List<Au> result = new List<Au>();
   
            foreach (DateTime dt in dateTimeList)
           {
                string data = HttpHelper.HttpGetOther(string.Format("http://dev.gosu.vn/api/188/au.{0}.json", dt.ToString("yyyyMMdd")), 0);
            //string json = "{\"au\":[{\"Timestamp\":1423353600,\"account\":\"a\",\"GameZone\":\"1004\",\"IP\":\"116.100.138.85\"},{\"Timestamp\":1423267200,\"account\":\"a\",\"GameZone\":\"1004\",\"IP\":\"116.100.138.85\"},{\"Timestamp\":1423267200,\"account\":\"c\",\"GameZone\":\"1004\",\"IP\":\"116.100.138.85\"},{\"Timestamp\":1423353600,\"account\":\"d\",\"GameZone\":\"1004\",\"IP\":\"113.171.213.140\"},{\"Timestamp\":1423440000,\"account\":\"e\",\"GameZone\":\"1004\",\"IP\":\"117.0.35.80\"},{\"Timestamp\":1423526400,\"account\":\"f\",\"GameZone\":\"1004\",\"IP\":\"117.0.35.80\"},{\"Timestamp\":1423612800,\"account\":\"g\",\"GameZone\":\"1004\",\"IP\":\"1.55.140.214\"},{\"Timestamp\":1423699200,\"account\":\"h\",\"GameZone\":\"1004\",\"IP\":\"113.184.183.59\"},{\"Timestamp\":1423267200,\"account\":\"j\",\"GameZone\":\"1004\",\"IP\":\"14.169.102.150\"},{\"Timestamp\":1423699200,\"account\":\"k\",\"GameZone\":\"1004\",\"IP\":\"14.169.102.150\"}]}";
                if (string.IsNullOrEmpty(data) == false)
                {
                    IList<Au> auList = new JavaScriptSerializer().Deserialize<IList<Au>>(data.Substring(6).Remove(data.Substring(6).Length - 1));
                    result.AddRange(auList);
                }
               //IList<Au> auList = new JavaScriptSerializer().Deserialize<IList<Au>>(json.Substring(6).Remove(json.Substring(6).Length - 1));    
            }
            return result;
        }

    }
}
